{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Dressmaker - Samples"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The data model of the tables.\n",
    "\n",
    "![](../src/img/dressmaker_str.svg)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdin",
     "output_type": "stream",
     "text": [
      " ········\n"
     ]
    }
   ],
   "source": [
    "import getpass\n",
    "import psycopg2\n",
    "from sqlalchemy import create_engine\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "pwd = getpass.getpass()\n",
    "engine = create_engine(\n",
    "    'postgresql+psycopg2://postgres:%s@192.168.31.31:15432/sqlzoo' % (pwd))\n",
    "pd.set_option('display.max_rows', 60)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "jmcust = pd.read_sql_table('jmcust', engine)\n",
    "dressmaker = pd.read_sql_table('dressmaker', engine)\n",
    "dress_order = pd.read_sql_table('dress_order', engine)\n",
    "construction = pd.read_sql_table('construction', engine)\n",
    "quantities = pd.read_sql_table('quantities', engine)\n",
    "order_line = pd.read_sql_table('order_line', engine)\n",
    "garment = pd.read_sql_table('garment', engine)\n",
    "material = pd.read_sql_table('material', engine)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1.\n",
    "The \"central\" table in this database is order_line - every garment ordered takes one line in this table. Many of the fields in this table are references to other tables. The fields of this table have the following meaning: \n",
    "\n",
    "- order_ref\n",
    "\n",
    "This is a link to the dress_order table. We can join the dress_order table to find information such as the the date of the order and the customer number for a given garment order.\n",
    "\n",
    "- line_no\n",
    "\n",
    "The line number is used to distinguish different items on the same order - for example order number 5 has three lines - 1, 2 and 3.\n",
    "\n",
    "- ol_style\n",
    "\n",
    "Indicates the article of clothing ordered. For example ol_style 1 indicates trousers - we can see this by joining to the garments table. Line 1 in the garment table is trousers.\n",
    "\n",
    "- ol_size\n",
    "\n",
    "The size of the item ordered is given here - this is particularly important when it comes to working out how much material is required to build the item. We can see from the quantities table that trousers (style 1) in size 8 takes 2.7 meters - whereas trousers in size 12 needs 2.8 meters.\n",
    "\n",
    "- ol_material\n",
    "\n",
    "Each order specifies the material to be used. We can join to the material table to find a description or cost per meter. Material 1 is Silk, Black, Plain costing £7 per meter.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>order_ref</th>\n",
       "      <th>line_no</th>\n",
       "      <th>ol_style</th>\n",
       "      <th>ol_size</th>\n",
       "      <th>ol_material</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>8</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>10</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>10</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>10</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   order_ref  line_no  ol_style  ol_size  ol_material\n",
       "0          1        1         1        8            1\n",
       "1          1        2         2        8            2\n",
       "2          2        1         3       10            3\n",
       "3          2        2         4       10            4\n",
       "4          2        3         5       10            5"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "order_line.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.\n",
    "A sample join:\n",
    "\n",
    "In order to translate the numbers in order_line into meaningful values we need to join a related table. For example if we want to access the descriptions of the materials we need to join the material table.\n",
    "\n",
    "To achieve the join we include the table material on the FROM line and the join condition as a WHERE clause.\n",
    "\n",
    "For each pair of tables there is a join condition between them (if they are linked). To find the join condition between order_line and material we look at the order_line table CREATE statement and notice the line that specifies that ol_material references the material table. This link will always be to the primary key of material table.\n",
    "\n",
    "```sql\n",
    "CREATE TABLE order_line (\n",
    "  order_ref\tINTEGER\tNOT NULL REFERENCES dress_order\n",
    " ,line_no\tINTEGER\tNOT NULL\n",
    " ,ol_style\tINTEGER\tREFERENCES garment\n",
    " ,ol_size\tINTEGER\tNOT NULL\n",
    " ,ol_material\tINTEGER\tREFERENCES material\n",
    " ,PRIMARY KEY (order_ref, line_no)\n",
    " ,FOREIGN KEY (ol_style, ol_size) REFERENCES quantities\n",
    " );\n",
    "SELECT order_ref, line_no, fabric, colour, pattern, cost\n",
    "  FROM order_line, material\n",
    " WHERE ol_material = material_no\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>order_ref</th>\n",
       "      <th>line_no</th>\n",
       "      <th>fabric</th>\n",
       "      <th>colour</th>\n",
       "      <th>pattern</th>\n",
       "      <th>cost</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Silk</td>\n",
       "      <td>Black</td>\n",
       "      <td>Plain</td>\n",
       "      <td>7.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>Silk</td>\n",
       "      <td>Black</td>\n",
       "      <td>Plain</td>\n",
       "      <td>7.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>12</td>\n",
       "      <td>3</td>\n",
       "      <td>Silk</td>\n",
       "      <td>Black</td>\n",
       "      <td>Plain</td>\n",
       "      <td>7.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>Silk</td>\n",
       "      <td>Red Abstract</td>\n",
       "      <td>Printed</td>\n",
       "      <td>10.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>7</td>\n",
       "      <td>2</td>\n",
       "      <td>Silk</td>\n",
       "      <td>Red Abstract</td>\n",
       "      <td>Printed</td>\n",
       "      <td>10.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>12</td>\n",
       "      <td>4</td>\n",
       "      <td>Silk</td>\n",
       "      <td>Red Abstract</td>\n",
       "      <td>Printed</td>\n",
       "      <td>10.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>Cotton</td>\n",
       "      <td>Yellow Stripe</td>\n",
       "      <td>Woven</td>\n",
       "      <td>3.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>7</td>\n",
       "      <td>3</td>\n",
       "      <td>Cotton</td>\n",
       "      <td>Yellow Stripe</td>\n",
       "      <td>Woven</td>\n",
       "      <td>3.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>12</td>\n",
       "      <td>5</td>\n",
       "      <td>Cotton</td>\n",
       "      <td>Yellow Stripe</td>\n",
       "      <td>Woven</td>\n",
       "      <td>3.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>Cotton</td>\n",
       "      <td>Green Stripe</td>\n",
       "      <td>Woven</td>\n",
       "      <td>3.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>8</td>\n",
       "      <td>1</td>\n",
       "      <td>Cotton</td>\n",
       "      <td>Green Stripe</td>\n",
       "      <td>Woven</td>\n",
       "      <td>3.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>Cotton</td>\n",
       "      <td>Black Dotted</td>\n",
       "      <td>Woven</td>\n",
       "      <td>3.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>8</td>\n",
       "      <td>2</td>\n",
       "      <td>Cotton</td>\n",
       "      <td>Black Dotted</td>\n",
       "      <td>Woven</td>\n",
       "      <td>3.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>Cotton</td>\n",
       "      <td>Red Stripe</td>\n",
       "      <td>Woven</td>\n",
       "      <td>3.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>8</td>\n",
       "      <td>3</td>\n",
       "      <td>Cotton</td>\n",
       "      <td>Red Stripe</td>\n",
       "      <td>Woven</td>\n",
       "      <td>3.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>Polyester</td>\n",
       "      <td>Pale Yellow</td>\n",
       "      <td>Printed</td>\n",
       "      <td>2.55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "      <td>Polyester</td>\n",
       "      <td>Pale Yellow</td>\n",
       "      <td>Printed</td>\n",
       "      <td>2.55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>Silk</td>\n",
       "      <td>Green Abstract</td>\n",
       "      <td>Printed</td>\n",
       "      <td>15.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>Silk</td>\n",
       "      <td>Green Abstract</td>\n",
       "      <td>Printed</td>\n",
       "      <td>15.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>11</td>\n",
       "      <td>1</td>\n",
       "      <td>Silk</td>\n",
       "      <td>Green Abstract</td>\n",
       "      <td>Printed</td>\n",
       "      <td>15.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>Cotton</td>\n",
       "      <td>Pink Check</td>\n",
       "      <td>Woven</td>\n",
       "      <td>3.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>10</td>\n",
       "      <td>2</td>\n",
       "      <td>Cotton</td>\n",
       "      <td>Pink Check</td>\n",
       "      <td>Woven</td>\n",
       "      <td>3.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>Rayon</td>\n",
       "      <td>Red/Orange</td>\n",
       "      <td>Printed</td>\n",
       "      <td>4.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>11</td>\n",
       "      <td>2</td>\n",
       "      <td>Rayon</td>\n",
       "      <td>Red/Orange</td>\n",
       "      <td>Printed</td>\n",
       "      <td>4.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>Serge</td>\n",
       "      <td>Navy Blue</td>\n",
       "      <td>Woven</td>\n",
       "      <td>5.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>11</td>\n",
       "      <td>3</td>\n",
       "      <td>Serge</td>\n",
       "      <td>Navy Blue</td>\n",
       "      <td>Woven</td>\n",
       "      <td>5.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>6</td>\n",
       "      <td>2</td>\n",
       "      <td>Cotton</td>\n",
       "      <td>Blue Abstract</td>\n",
       "      <td>Printed</td>\n",
       "      <td>3.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>12</td>\n",
       "      <td>1</td>\n",
       "      <td>Cotton</td>\n",
       "      <td>Blue Abstract</td>\n",
       "      <td>Printed</td>\n",
       "      <td>3.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>6</td>\n",
       "      <td>3</td>\n",
       "      <td>Cotton</td>\n",
       "      <td>Green Abstract</td>\n",
       "      <td>Printed</td>\n",
       "      <td>3.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>12</td>\n",
       "      <td>2</td>\n",
       "      <td>Cotton</td>\n",
       "      <td>Green Abstract</td>\n",
       "      <td>Printed</td>\n",
       "      <td>3.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "      <td>Cotton</td>\n",
       "      <td>Blue Stripe</td>\n",
       "      <td>Woven</td>\n",
       "      <td>3.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    order_ref  line_no                fabric                colour  \\\n",
       "0           1        1  Silk                  Black                  \n",
       "1           7        1  Silk                  Black                  \n",
       "2          12        3  Silk                  Black                  \n",
       "3           1        2  Silk                  Red Abstract           \n",
       "4           7        2  Silk                  Red Abstract           \n",
       "5          12        4  Silk                  Red Abstract           \n",
       "6           2        1  Cotton                Yellow Stripe          \n",
       "7           7        3  Cotton                Yellow Stripe          \n",
       "8          12        5  Cotton                Yellow Stripe          \n",
       "9           2        2  Cotton                Green Stripe           \n",
       "10          8        1  Cotton                Green Stripe           \n",
       "11          2        3  Cotton                Black Dotted           \n",
       "12          8        2  Cotton                Black Dotted           \n",
       "13          3        1  Cotton                Red Stripe             \n",
       "14          8        3  Cotton                Red Stripe             \n",
       "15          4        1  Polyester             Pale Yellow            \n",
       "16          9        1  Polyester             Pale Yellow            \n",
       "17          4        2  Silk                  Green Abstract         \n",
       "18          5        2  Silk                  Green Abstract         \n",
       "19         11        1  Silk                  Green Abstract         \n",
       "20          5        1  Cotton                Pink Check             \n",
       "21         10        2  Cotton                Pink Check             \n",
       "22          5        3  Rayon                 Red/Orange             \n",
       "23         11        2  Rayon                 Red/Orange             \n",
       "24          6        1  Serge                 Navy Blue              \n",
       "25         11        3  Serge                 Navy Blue              \n",
       "26          6        2  Cotton                Blue Abstract          \n",
       "27         12        1  Cotton                Blue Abstract          \n",
       "28          6        3  Cotton                Green Abstract         \n",
       "29         12        2  Cotton                Green Abstract         \n",
       "30         10        1  Cotton                Blue Stripe            \n",
       "\n",
       "                 pattern   cost  \n",
       "0   Plain                  7.00  \n",
       "1   Plain                  7.00  \n",
       "2   Plain                  7.00  \n",
       "3   Printed               10.00  \n",
       "4   Printed               10.00  \n",
       "5   Printed               10.00  \n",
       "6   Woven                  3.00  \n",
       "7   Woven                  3.00  \n",
       "8   Woven                  3.00  \n",
       "9   Woven                  3.00  \n",
       "10  Woven                  3.00  \n",
       "11  Woven                  3.00  \n",
       "12  Woven                  3.00  \n",
       "13  Woven                  3.00  \n",
       "14  Woven                  3.00  \n",
       "15  Printed                2.55  \n",
       "16  Printed                2.55  \n",
       "17  Printed               15.00  \n",
       "18  Printed               15.00  \n",
       "19  Printed               15.00  \n",
       "20  Woven                  3.00  \n",
       "21  Woven                  3.00  \n",
       "22  Printed                4.00  \n",
       "23  Printed                4.00  \n",
       "24  Woven                  5.50  \n",
       "25  Woven                  5.50  \n",
       "26  Printed                3.50  \n",
       "27  Printed                3.50  \n",
       "28  Printed                3.50  \n",
       "29  Printed                3.50  \n",
       "30  Woven                  3.00  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(order_line.merge(material, left_on='ol_material', right_on='material_no')\n",
    " [['order_ref', 'line_no', 'fabric', 'colour', 'pattern', 'cost']])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3.\n",
    "To get a description of the garment we need to join the garment table. The join condition is that the ol_style in order_line matches the style_no in garment.\n",
    "\n",
    "```sql\n",
    "SELECT order_ref, line_no, description\n",
    "  FROM order_line, garment\n",
    " WHERE ol_style = style_no\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>order_ref</th>\n",
       "      <th>line_no</th>\n",
       "      <th>description</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Trousers</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>Trousers</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>Trousers</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10</td>\n",
       "      <td>2</td>\n",
       "      <td>Trousers</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>11</td>\n",
       "      <td>1</td>\n",
       "      <td>Trousers</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>12</td>\n",
       "      <td>4</td>\n",
       "      <td>Trousers</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>Long Skirt</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>Long Skirt</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>6</td>\n",
       "      <td>2</td>\n",
       "      <td>Long Skirt</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "      <td>Long Skirt</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>11</td>\n",
       "      <td>2</td>\n",
       "      <td>Long Skirt</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>12</td>\n",
       "      <td>5</td>\n",
       "      <td>Long Skirt</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>Shorts</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>Shorts</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>6</td>\n",
       "      <td>3</td>\n",
       "      <td>Shorts</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "      <td>Shorts</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>11</td>\n",
       "      <td>3</td>\n",
       "      <td>Shorts</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>Short Skirt</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>Short Skirt</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>Short Skirt</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>8</td>\n",
       "      <td>3</td>\n",
       "      <td>Short Skirt</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>12</td>\n",
       "      <td>1</td>\n",
       "      <td>Short Skirt</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>Sundress</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>Sundress</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>7</td>\n",
       "      <td>2</td>\n",
       "      <td>Sundress</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>8</td>\n",
       "      <td>2</td>\n",
       "      <td>Sundress</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>12</td>\n",
       "      <td>2</td>\n",
       "      <td>Sundress</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>Suntop</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>7</td>\n",
       "      <td>3</td>\n",
       "      <td>Suntop</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>8</td>\n",
       "      <td>1</td>\n",
       "      <td>Suntop</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>12</td>\n",
       "      <td>3</td>\n",
       "      <td>Suntop</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    order_ref  line_no           description\n",
       "0           1        1  Trousers            \n",
       "1           4        1  Trousers            \n",
       "2           6        1  Trousers            \n",
       "3          10        2  Trousers            \n",
       "4          11        1  Trousers            \n",
       "5          12        4  Trousers            \n",
       "6           1        2  Long Skirt          \n",
       "7           4        2  Long Skirt          \n",
       "8           6        2  Long Skirt          \n",
       "9          10        1  Long Skirt          \n",
       "10         11        2  Long Skirt          \n",
       "11         12        5  Long Skirt          \n",
       "12          2        1  Shorts              \n",
       "13          5        1  Shorts              \n",
       "14          6        3  Shorts              \n",
       "15          9        1  Shorts              \n",
       "16         11        3  Shorts              \n",
       "17          2        2  Short Skirt         \n",
       "18          5        2  Short Skirt         \n",
       "19          7        1  Short Skirt         \n",
       "20          8        3  Short Skirt         \n",
       "21         12        1  Short Skirt         \n",
       "22          2        3  Sundress            \n",
       "23          5        3  Sundress            \n",
       "24          7        2  Sundress            \n",
       "25          8        2  Sundress            \n",
       "26         12        2  Sundress            \n",
       "27          3        1  Suntop              \n",
       "28          7        3  Suntop              \n",
       "29          8        1  Suntop              \n",
       "30         12        3  Suntop              "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(order_line.merge(garment, left_on='ol_style', right_on='style_no')\n",
    " [['order_ref', 'line_no', 'description']])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4.\n",
    "If we need both the description and the fabric we can join both material and garment to the order_line table. The join conditions are combined with \"AND\"\n",
    "\n",
    "```sql\n",
    "SELECT order_ref, line_no, fabric, description\n",
    "  FROM order_line, material, garment\n",
    " WHERE ol_style = style_no\n",
    "   AND ol_material = material_no\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>order_ref</th>\n",
       "      <th>line_no</th>\n",
       "      <th>fabric</th>\n",
       "      <th>description</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Silk</td>\n",
       "      <td>Trousers</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>12</td>\n",
       "      <td>4</td>\n",
       "      <td>Silk</td>\n",
       "      <td>Trousers</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>Polyester</td>\n",
       "      <td>Trousers</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>11</td>\n",
       "      <td>1</td>\n",
       "      <td>Silk</td>\n",
       "      <td>Trousers</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10</td>\n",
       "      <td>2</td>\n",
       "      <td>Cotton</td>\n",
       "      <td>Trousers</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>Serge</td>\n",
       "      <td>Trousers</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>Silk</td>\n",
       "      <td>Short Skirt</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>Cotton</td>\n",
       "      <td>Short Skirt</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>8</td>\n",
       "      <td>3</td>\n",
       "      <td>Cotton</td>\n",
       "      <td>Short Skirt</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>Silk</td>\n",
       "      <td>Short Skirt</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>12</td>\n",
       "      <td>1</td>\n",
       "      <td>Cotton</td>\n",
       "      <td>Short Skirt</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>12</td>\n",
       "      <td>3</td>\n",
       "      <td>Silk</td>\n",
       "      <td>Suntop</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>7</td>\n",
       "      <td>3</td>\n",
       "      <td>Cotton</td>\n",
       "      <td>Suntop</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>8</td>\n",
       "      <td>1</td>\n",
       "      <td>Cotton</td>\n",
       "      <td>Suntop</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>Cotton</td>\n",
       "      <td>Suntop</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>Silk</td>\n",
       "      <td>Long Skirt</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>12</td>\n",
       "      <td>5</td>\n",
       "      <td>Cotton</td>\n",
       "      <td>Long Skirt</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>Silk</td>\n",
       "      <td>Long Skirt</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>11</td>\n",
       "      <td>2</td>\n",
       "      <td>Rayon</td>\n",
       "      <td>Long Skirt</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>6</td>\n",
       "      <td>2</td>\n",
       "      <td>Cotton</td>\n",
       "      <td>Long Skirt</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "      <td>Cotton</td>\n",
       "      <td>Long Skirt</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>7</td>\n",
       "      <td>2</td>\n",
       "      <td>Silk</td>\n",
       "      <td>Sundress</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>Cotton</td>\n",
       "      <td>Sundress</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>8</td>\n",
       "      <td>2</td>\n",
       "      <td>Cotton</td>\n",
       "      <td>Sundress</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>Rayon</td>\n",
       "      <td>Sundress</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>12</td>\n",
       "      <td>2</td>\n",
       "      <td>Cotton</td>\n",
       "      <td>Sundress</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>Cotton</td>\n",
       "      <td>Shorts</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "      <td>Polyester</td>\n",
       "      <td>Shorts</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>Cotton</td>\n",
       "      <td>Shorts</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>11</td>\n",
       "      <td>3</td>\n",
       "      <td>Serge</td>\n",
       "      <td>Shorts</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>6</td>\n",
       "      <td>3</td>\n",
       "      <td>Cotton</td>\n",
       "      <td>Shorts</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    order_ref  line_no                fabric           description\n",
       "0           1        1  Silk                  Trousers            \n",
       "1          12        4  Silk                  Trousers            \n",
       "2           4        1  Polyester             Trousers            \n",
       "3          11        1  Silk                  Trousers            \n",
       "4          10        2  Cotton                Trousers            \n",
       "5           6        1  Serge                 Trousers            \n",
       "6           7        1  Silk                  Short Skirt         \n",
       "7           2        2  Cotton                Short Skirt         \n",
       "8           8        3  Cotton                Short Skirt         \n",
       "9           5        2  Silk                  Short Skirt         \n",
       "10         12        1  Cotton                Short Skirt         \n",
       "11         12        3  Silk                  Suntop              \n",
       "12          7        3  Cotton                Suntop              \n",
       "13          8        1  Cotton                Suntop              \n",
       "14          3        1  Cotton                Suntop              \n",
       "15          1        2  Silk                  Long Skirt          \n",
       "16         12        5  Cotton                Long Skirt          \n",
       "17          4        2  Silk                  Long Skirt          \n",
       "18         11        2  Rayon                 Long Skirt          \n",
       "19          6        2  Cotton                Long Skirt          \n",
       "20         10        1  Cotton                Long Skirt          \n",
       "21          7        2  Silk                  Sundress            \n",
       "22          2        3  Cotton                Sundress            \n",
       "23          8        2  Cotton                Sundress            \n",
       "24          5        3  Rayon                 Sundress            \n",
       "25         12        2  Cotton                Sundress            \n",
       "26          2        1  Cotton                Shorts              \n",
       "27          9        1  Polyester             Shorts              \n",
       "28          5        1  Cotton                Shorts              \n",
       "29         11        3  Serge                 Shorts              \n",
       "30          6        3  Cotton                Shorts              "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(order_line.merge(material, left_on='ol_material', right_on='material_no')\n",
    " .merge(garment, left_on='ol_style', right_on='style_no')\n",
    " [['order_ref', 'line_no', 'fabric', 'description']])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5.\n",
    "The quantities table tells us how much material is required for every garment for every size available. The join between the order_line and quantities is unusual in that it involves two fields. This can be seen by the fact that quantities has a composite key.\n",
    "\n",
    "```sql\n",
    "SELECT order_ref, line_no, quantity\n",
    "  FROM order_line, quantities\n",
    " WHERE ol_style = style_q\n",
    "   AND ol_size  = size_q\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>order_ref</th>\n",
       "      <th>line_no</th>\n",
       "      <th>quantity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>2.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>12</td>\n",
       "      <td>4</td>\n",
       "      <td>2.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>6</td>\n",
       "      <td>2</td>\n",
       "      <td>3.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>12</td>\n",
       "      <td>5</td>\n",
       "      <td>3.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>1.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>1.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>3.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>7</td>\n",
       "      <td>2</td>\n",
       "      <td>3.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>8</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>2.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>3.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>1.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>1.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>5.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>6</td>\n",
       "      <td>3</td>\n",
       "      <td>1.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>7</td>\n",
       "      <td>3</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>8</td>\n",
       "      <td>2</td>\n",
       "      <td>3.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>8</td>\n",
       "      <td>3</td>\n",
       "      <td>1.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "      <td>1.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "      <td>4.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>10</td>\n",
       "      <td>2</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>11</td>\n",
       "      <td>1</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>11</td>\n",
       "      <td>2</td>\n",
       "      <td>4.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>11</td>\n",
       "      <td>3</td>\n",
       "      <td>1.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>12</td>\n",
       "      <td>1</td>\n",
       "      <td>1.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>12</td>\n",
       "      <td>2</td>\n",
       "      <td>3.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>12</td>\n",
       "      <td>3</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    order_ref  line_no  quantity\n",
       "0           1        1       2.7\n",
       "1           6        1       2.7\n",
       "2          12        4       2.7\n",
       "3           1        2       3.4\n",
       "4           6        2       3.4\n",
       "5          12        5       3.4\n",
       "6           2        1       1.3\n",
       "7           2        2       1.2\n",
       "8           7        1       1.2\n",
       "9           2        3       3.2\n",
       "10          7        2       3.2\n",
       "11          3        1       1.0\n",
       "12          8        1       1.0\n",
       "13          4        1       2.8\n",
       "14          4        2       3.8\n",
       "15          5        1       1.6\n",
       "16          5        2       1.5\n",
       "17          5        3       5.2\n",
       "18          6        3       1.3\n",
       "19          7        3       1.0\n",
       "20          8        2       3.2\n",
       "21          8        3       1.2\n",
       "22          9        1       1.5\n",
       "23         10        1       4.2\n",
       "24         10        2       3.0\n",
       "25         11        1       3.0\n",
       "26         11        2       4.5\n",
       "27         11        3       1.8\n",
       "28         12        1       1.2\n",
       "29         12        2       3.2\n",
       "30         12        3       1.0"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(order_line.merge(quantities, left_on=['ol_style', 'ol_size'],\n",
    "                  right_on=['style_q', 'size_q'])\n",
    " [['order_ref', 'line_no', 'quantity']])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 6.\n",
    "Customers place orders - each order contains many lines - each line of the order refers to a garment:\n",
    "\n",
    "```sql\n",
    "SELECT c_name, order_date, order_no, line_no\n",
    "   FROM jmcust, dress_order, order_line\n",
    "  WHERE          jmcust.c_no = dress_order.cust_no\n",
    "  AND   dress_order.order_no = order_line.order_ref\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>c_name</th>\n",
       "      <th>order_date</th>\n",
       "      <th>order_no</th>\n",
       "      <th>line_no</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Ms Black</td>\n",
       "      <td>2002-02-27</td>\n",
       "      <td>8</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Ms Black</td>\n",
       "      <td>2002-02-27</td>\n",
       "      <td>8</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Ms Black</td>\n",
       "      <td>2002-02-27</td>\n",
       "      <td>8</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Ms Brown</td>\n",
       "      <td>2002-02-21</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Ms Brown</td>\n",
       "      <td>2002-02-21</td>\n",
       "      <td>7</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Ms Brown</td>\n",
       "      <td>2002-02-21</td>\n",
       "      <td>7</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Ms Brown</td>\n",
       "      <td>2002-02-27</td>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Ms Gray</td>\n",
       "      <td>2002-02-20</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Ms Gray</td>\n",
       "      <td>2002-02-20</td>\n",
       "      <td>6</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Ms Gray</td>\n",
       "      <td>2002-02-20</td>\n",
       "      <td>6</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Ms Gray</td>\n",
       "      <td>2002-02-28</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Ms Gray</td>\n",
       "      <td>2002-02-28</td>\n",
       "      <td>10</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Ms White</td>\n",
       "      <td>2002-02-03</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Ms White</td>\n",
       "      <td>2002-02-03</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Ms White</td>\n",
       "      <td>2002-02-03</td>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Ms White</td>\n",
       "      <td>2002-03-01</td>\n",
       "      <td>11</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Ms White</td>\n",
       "      <td>2002-03-01</td>\n",
       "      <td>11</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Ms White</td>\n",
       "      <td>2002-03-01</td>\n",
       "      <td>11</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Mr Brass</td>\n",
       "      <td>2002-02-02</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Mr Brass</td>\n",
       "      <td>2002-02-02</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Mr Brass</td>\n",
       "      <td>2002-03-03</td>\n",
       "      <td>12</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Mr Brass</td>\n",
       "      <td>2002-03-03</td>\n",
       "      <td>12</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>Mr Brass</td>\n",
       "      <td>2002-03-03</td>\n",
       "      <td>12</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Mr Brass</td>\n",
       "      <td>2002-03-03</td>\n",
       "      <td>12</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>Mr Brass</td>\n",
       "      <td>2002-03-03</td>\n",
       "      <td>12</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>Ms Muir</td>\n",
       "      <td>2002-01-20</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>Dr Green</td>\n",
       "      <td>2002-01-11</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>Dr Green</td>\n",
       "      <td>2002-01-11</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>Dr Green</td>\n",
       "      <td>2002-01-11</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>Mrs Peacock</td>\n",
       "      <td>2002-01-10</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>Mrs Peacock</td>\n",
       "      <td>2002-01-10</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  c_name order_date  order_no  line_no\n",
       "0   Ms Black             2002-02-27         8        1\n",
       "1   Ms Black             2002-02-27         8        2\n",
       "2   Ms Black             2002-02-27         8        3\n",
       "3   Ms Brown             2002-02-21         7        1\n",
       "4   Ms Brown             2002-02-21         7        2\n",
       "5   Ms Brown             2002-02-21         7        3\n",
       "6   Ms Brown             2002-02-27         9        1\n",
       "7   Ms Gray              2002-02-20         6        1\n",
       "8   Ms Gray              2002-02-20         6        2\n",
       "9   Ms Gray              2002-02-20         6        3\n",
       "10  Ms Gray              2002-02-28        10        1\n",
       "11  Ms Gray              2002-02-28        10        2\n",
       "12  Ms White             2002-02-03         5        1\n",
       "13  Ms White             2002-02-03         5        2\n",
       "14  Ms White             2002-02-03         5        3\n",
       "15  Ms White             2002-03-01        11        1\n",
       "16  Ms White             2002-03-01        11        2\n",
       "17  Ms White             2002-03-01        11        3\n",
       "18  Mr Brass             2002-02-02         4        1\n",
       "19  Mr Brass             2002-02-02         4        2\n",
       "20  Mr Brass             2002-03-03        12        1\n",
       "21  Mr Brass             2002-03-03        12        2\n",
       "22  Mr Brass             2002-03-03        12        3\n",
       "23  Mr Brass             2002-03-03        12        4\n",
       "24  Mr Brass             2002-03-03        12        5\n",
       "25  Ms Muir              2002-01-20         3        1\n",
       "26  Dr Green             2002-01-11         2        1\n",
       "27  Dr Green             2002-01-11         2        2\n",
       "28  Dr Green             2002-01-11         2        3\n",
       "29  Mrs Peacock          2002-01-10         1        1\n",
       "30  Mrs Peacock          2002-01-10         1        2"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(jmcust.merge(dress_order, left_on='c_no', right_on='cust_no')\n",
    " .merge(order_line, left_on='order_no', right_on='order_ref')\n",
    " [['c_name', 'order_date', 'order_no', 'line_no']])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 7.\n",
    "There's also a dress maker table, and a table called construction which gives you information about who made which order and when:\n",
    "\n",
    "```sql\n",
    "SELECT d_no, d_name, construction.order_ref, construction.line_ref, start_date, finish_date\n",
    " FROM dressmaker, order_line, construction\n",
    " WHERE  d_no=maker  \n",
    " AND order_line.order_ref=construction.order_ref \n",
    " AND order_line.line_no=construction.line_ref\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>d_no</th>\n",
       "      <th>d_name</th>\n",
       "      <th>order_ref</th>\n",
       "      <th>line_ref</th>\n",
       "      <th>start_date</th>\n",
       "      <th>finish_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Mrs Hem</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2002-01-10</td>\n",
       "      <td>2002-03-05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>Mrs Hem</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>2002-02-02</td>\n",
       "      <td>2002-03-25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>Mrs Hem</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>2002-02-21</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>Mrs Hem</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "      <td>2002-02-28</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>Mrs Hem</td>\n",
       "      <td>12</td>\n",
       "      <td>3</td>\n",
       "      <td>2002-03-03</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2</td>\n",
       "      <td>Miss Stitch</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2002-01-10</td>\n",
       "      <td>2002-03-15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2</td>\n",
       "      <td>Miss Stitch</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>2002-02-03</td>\n",
       "      <td>2002-03-15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2</td>\n",
       "      <td>Miss Stitch</td>\n",
       "      <td>7</td>\n",
       "      <td>2</td>\n",
       "      <td>2002-02-21</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2</td>\n",
       "      <td>Miss Stitch</td>\n",
       "      <td>10</td>\n",
       "      <td>2</td>\n",
       "      <td>2002-03-28</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2</td>\n",
       "      <td>Miss Stitch</td>\n",
       "      <td>12</td>\n",
       "      <td>4</td>\n",
       "      <td>2002-03-03</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>2</td>\n",
       "      <td>Miss Stitch</td>\n",
       "      <td>12</td>\n",
       "      <td>5</td>\n",
       "      <td>2002-03-03</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>3</td>\n",
       "      <td>Mr Needles</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2002-01-11</td>\n",
       "      <td>2002-03-05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>3</td>\n",
       "      <td>Mr Needles</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>2002-02-03</td>\n",
       "      <td>2002-03-25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>3</td>\n",
       "      <td>Mr Needles</td>\n",
       "      <td>7</td>\n",
       "      <td>3</td>\n",
       "      <td>2002-02-21</td>\n",
       "      <td>2002-03-28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>3</td>\n",
       "      <td>Mr Needles</td>\n",
       "      <td>11</td>\n",
       "      <td>1</td>\n",
       "      <td>2002-03-01</td>\n",
       "      <td>2002-03-04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>4</td>\n",
       "      <td>Ms Sew</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2002-01-11</td>\n",
       "      <td>2002-03-25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>4</td>\n",
       "      <td>Ms Sew</td>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>2002-02-03</td>\n",
       "      <td>2002-03-27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>4</td>\n",
       "      <td>Ms Sew</td>\n",
       "      <td>8</td>\n",
       "      <td>1</td>\n",
       "      <td>2002-02-27</td>\n",
       "      <td>2002-03-03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>4</td>\n",
       "      <td>Ms Sew</td>\n",
       "      <td>11</td>\n",
       "      <td>2</td>\n",
       "      <td>2002-03-01</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>5</td>\n",
       "      <td>Mr Seam</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>2002-01-11</td>\n",
       "      <td>2002-03-05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>5</td>\n",
       "      <td>Mr Seam</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>2002-02-20</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>5</td>\n",
       "      <td>Mr Seam</td>\n",
       "      <td>8</td>\n",
       "      <td>2</td>\n",
       "      <td>2002-02-27</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>5</td>\n",
       "      <td>Mr Seam</td>\n",
       "      <td>11</td>\n",
       "      <td>3</td>\n",
       "      <td>2002-03-01</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>6</td>\n",
       "      <td>Mr Taylor</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>2002-01-20</td>\n",
       "      <td>2002-03-25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>6</td>\n",
       "      <td>Mr Taylor</td>\n",
       "      <td>6</td>\n",
       "      <td>2</td>\n",
       "      <td>2002-02-20</td>\n",
       "      <td>2002-03-28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>6</td>\n",
       "      <td>Mr Taylor</td>\n",
       "      <td>8</td>\n",
       "      <td>3</td>\n",
       "      <td>2002-02-27</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>7</td>\n",
       "      <td>Miss Pins</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>2002-02-02</td>\n",
       "      <td>2002-03-05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>7</td>\n",
       "      <td>Miss Pins</td>\n",
       "      <td>6</td>\n",
       "      <td>3</td>\n",
       "      <td>2002-02-20</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>7</td>\n",
       "      <td>Miss Pins</td>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "      <td>2002-02-27</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>7</td>\n",
       "      <td>Miss Pins</td>\n",
       "      <td>12</td>\n",
       "      <td>2</td>\n",
       "      <td>2002-03-03</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    d_no                d_name  order_ref  line_ref start_date finish_date\n",
       "0      1  Mrs Hem                       1         1 2002-01-10  2002-03-05\n",
       "1      1  Mrs Hem                       4         2 2002-02-02  2002-03-25\n",
       "2      1  Mrs Hem                       7         1 2002-02-21         NaT\n",
       "3      1  Mrs Hem                      10         1 2002-02-28         NaT\n",
       "4      1  Mrs Hem                      12         3 2002-03-03         NaT\n",
       "5      2  Miss Stitch                   1         2 2002-01-10  2002-03-15\n",
       "6      2  Miss Stitch                   5         1 2002-02-03  2002-03-15\n",
       "7      2  Miss Stitch                   7         2 2002-02-21         NaT\n",
       "8      2  Miss Stitch                  10         2 2002-03-28         NaT\n",
       "9      2  Miss Stitch                  12         4 2002-03-03         NaT\n",
       "10     2  Miss Stitch                  12         5 2002-03-03         NaT\n",
       "11     3  Mr Needles                    2         1 2002-01-11  2002-03-05\n",
       "12     3  Mr Needles                    5         2 2002-02-03  2002-03-25\n",
       "13     3  Mr Needles                    7         3 2002-02-21  2002-03-28\n",
       "14     3  Mr Needles                   11         1 2002-03-01  2002-03-04\n",
       "15     4  Ms Sew                        2         2 2002-01-11  2002-03-25\n",
       "16     4  Ms Sew                        5         3 2002-02-03  2002-03-27\n",
       "17     4  Ms Sew                        8         1 2002-02-27  2002-03-03\n",
       "18     4  Ms Sew                       11         2 2002-03-01         NaT\n",
       "19     5  Mr Seam                       2         3 2002-01-11  2002-03-05\n",
       "20     5  Mr Seam                       6         1 2002-02-20         NaT\n",
       "21     5  Mr Seam                       8         2 2002-02-27         NaT\n",
       "22     5  Mr Seam                      11         3 2002-03-01         NaT\n",
       "23     6  Mr Taylor                     3         1 2002-01-20  2002-03-25\n",
       "24     6  Mr Taylor                     6         2 2002-02-20  2002-03-28\n",
       "25     6  Mr Taylor                     8         3 2002-02-27         NaT\n",
       "26     7  Miss Pins                     4         1 2002-02-02  2002-03-05\n",
       "27     7  Miss Pins                     6         3 2002-02-20         NaT\n",
       "28     7  Miss Pins                     9         1 2002-02-27         NaT\n",
       "29     7  Miss Pins                    12         2 2002-03-03         NaT"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(dressmaker.merge(construction, left_on='d_no', right_on='maker')\n",
    " .merge(order_line, left_on=['order_ref', 'line_ref'],\n",
    "        right_on=['order_ref', 'line_no'])\n",
    " [['d_no', 'd_name', 'order_ref', 'line_ref', 'start_date', 'finish_date']])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
